package com.whisperarts.financer.dao;

import static com.whisperarts.financer.model.account.Account.CATEGORY;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

import com.whisperarts.financer.model.account.Account;
import com.whisperarts.financer.model.account.AccountsCategory;
import com.whisperarts.financer.model.currency.Currency;
import com.whisperarts.financer.model.transaction.Category;
import com.whisperarts.financer.model.transaction.Transaction;

public class AccountsDAO {

	private static final String CONNECTION_STRING = "jdbc:sqlite:/home/artem/test.db";

	private static AccountsCategory[] categories = {new AccountsCategory(CATEGORY[0]),
			new AccountsCategory(CATEGORY[1]),
			new AccountsCategory(CATEGORY[2]),
			new AccountsCategory(CATEGORY[3]),
			new AccountsCategory(CATEGORY[4]) };
	
	private static final Map<Integer, Category> txCategories = new HashMap<Integer, Category>();
	
	private static Map<String, Currency> currencies; 

	public static boolean initialized = false;
	
	static {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			log(e);
		}
	}
	
	public static AccountsCategory[] getAccountsCategories() {
		if (!initialized)
			fillCategories();
		return categories;
	}
	
	public static Map<Integer, Category> getCategories() {
		return txCategories;
	}
	
	public static Account getAccountById(int id) {
		for (AccountsCategory category : categories) {
			for (Account account : category.getAccounts()) {
				if (account.getId() == id)
					return account;
			}
		}
		return null;
	}
		
	private static void fillCategories() {
		long start = System.currentTimeMillis();
		Connection conn;
		ResultSet rs;
		try {
			fillCurrencies();
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();

			rs = stat.executeQuery("SELECT * FROM accounts WHERE deleted=0;");
			while (rs.next()) {
				Account account = new Account();
				account.setId( rs.getInt("id") );
				account.setName( rs.getString("name") );
				Calendar calendar = Calendar.getInstance();
				calendar.setTime( rs.getDate("date_of_creation") );
				account.setDateOfCreation(calendar);
				account.setOpeningAmount( new BigDecimal(rs.getString("opening_amount")) );
				account.setCurrency( currencies.get( rs.getString("currency") ) );
				account.setAccountType( rs.getInt("account_type"));
				account.setNote( rs.getString("note"));

				account.getTransactions().add(new Transaction(-1, 
															  new Category(-1, "Opening account", true, -1), 
															  account.getOpeningAmount(),
															  account.getDateOfCreation(),
															  true,
															  "",
															  account.getId()));
				
				categories[account.getAccountType()].getAccounts().add(account);
			}
			rs.close();

			rs = stat.executeQuery("SELECT * FROM tx_categories WHERE deleted=0 ORDER BY id;");
			while (rs.next()) {
				Category txCategory = new Category();
				txCategory.setId( rs.getInt("id") );
				txCategory.setName( rs.getString("name") );
				txCategory.setIncome( rs.getBoolean("income") );
				txCategory.setParentId( rs.getInt("parent_id") );
				
				txCategories.put(txCategory.getId(), txCategory);
			}
			rs.close();
			organizeCategories();

			rs = stat.executeQuery("SELECT * FROM transactions WHERE deleted=0;");
			while (rs.next()) {
				Transaction transaction = new Transaction();
				transaction.setId( rs.getInt("id") );
				transaction.setCategory( txCategories.get(rs.getInt("category_id")) );
				transaction.setAmount( new BigDecimal( rs.getString("amount") ) );
				Calendar calendar = Calendar.getInstance();
				calendar.setTime( rs.getDate("date_of_creation") );
				transaction.setDate(calendar);
				transaction.setIncome( rs.getBoolean("income") );
				transaction.setNote( rs.getString("note"));
				int accountId = rs.getInt("account_id");
				transaction.setAccountId( accountId );
				
				Account account = getAccountById(accountId);
				if (account != null) {
					account.getTransactions().add(transaction);
				}
			}
			rs.close();
			conn.close();   
			initialized = true;
		} catch (SQLException e ) {
			log(e);
		}
		long end = System.currentTimeMillis();
		System.err.println("DB info loaded in: " + (end - start));		
	}
	
	private static void organizeCategories() {
		for (Category category : txCategories.values()) {
			int parentId = category.getParentId(); 
			if (parentId != -1)
				txCategories.get(parentId).addSub(category);
		}
	}

	public static int saveAccount(Account account) {
		Connection conn;
		ResultSet rs;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			
			stat.execute("INSERT INTO accounts(name, date_of_creation, opening_amount, currency, account_type, note) " +
									"VALUES('" + account.getName() + "', '" +
											 account.getDateOfCreation() + "', " + 
											 account.getOpeningAmount() + ", '" + 
											 account.getCurrency().getCode() +  "', " +
											 account.getAccountType() + ", '" +
											 account.getNote() + "');");
			rs = stat.executeQuery("SELECT last_insert_rowid();");
			rs.next();
			int id = rs.getInt(1);
			account.setId(id);
			rs.close();
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		
		return 0;
	}
	
	private static void log(Exception e) {
		e.printStackTrace();
	}

	public static int deleteAccount(Account account) {
		Connection conn;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			stat.executeUpdate("UPDATE accounts SET deleted=1 WHERE id = " + account.getId() + ";");
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		
		return 0;
	}
	
	public static int updateAccount(Account account) {
		Connection conn;
		ResultSet rs;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			
			String query = "UPDATE accounts SET name = '" + account.getName() + "', " +
												"date_of_creation = '" + account.getDateOfCreation() + "', " + 
												"opening_amount = "	 + account.getOpeningAmount() + ", " + 
												"currency = '" + account.getCurrency().getCode() +  "', " +
												"account_type = " + account.getAccountType() + ", " +
												"note = '" + account.getNote() + "' ";
			query += "WHERE id = " + account.getId();
			
			stat.execute(query);			
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		return 0;
	}
	
	public static int saveCategory(Category category) {
		Connection conn;
		ResultSet rs;
		int id;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			
			stat.execute("INSERT INTO tx_categories(name, income, parent_id) " +
									"VALUES('" + category.getName() + "', " +
											 (category.isIncome() ? 1 : 0) + ", " +
											 category.getParentId() + ");");
			rs = stat.executeQuery("SELECT last_insert_rowid();");
			rs.next();
			id = rs.getInt(1);
			category.setId(id);
			rs.close();
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		return 0;
	}

	public static int deleteCategory(Category category) {
		Connection conn;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			stat.executeUpdate("UPDATE tx_categories SET deleted=1 WHERE id = " + category.getId() + ";");
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		
		return 0;
	}

	public static int deleteTransaction(Transaction tx) {
		Connection conn;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			stat.executeUpdate("UPDATE transactions SET deleted=1 WHERE id = " + tx.getId() + ";");
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		
		return 0;
	}

	public static int saveTransaction(Transaction tx) {
		Connection conn;
		ResultSet rs;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			
			stat.execute("INSERT INTO transactions(category_id, amount, date_of_creation, income, note, account_id) " +
									"VALUES(" + tx.getCategory().getId() + ", '" +
											 tx.getAmount() + "', " + 
											 tx.getDate().getTimeInMillis() + ", " + 
											 (tx.isIncome() ? 1 : 0) + ", '" +
											 tx.getNote() +  "', " +
											 tx.getAccountId() + ");");
			rs = stat.executeQuery("SELECT last_insert_rowid();");
			rs.next();
			int id = rs.getInt(1);
			tx.setId(id);
			rs.close();
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		
		return 0;
	}
	
	public static Map<String, Currency> getCurrencies() {
		if (currencies == null)
			fillCurrencies();
		return currencies;
	}

	private static void fillCurrencies() {
		currencies = new HashMap<String, Currency>();
		currencies.put("RUB", new Currency("RUB", "p."));		
		currencies.put("USD", new Currency("USD", "$ "));		
		currencies.put("EUR", new Currency("EUR", "€ "));		
	}

	public static int updateCategory(Category category) {
		Connection conn;
		try {
			conn = DriverManager.getConnection(CONNECTION_STRING);
			Statement stat = conn.createStatement();
			
			stat.execute("UPDATE tx_categories SET " +
											"name = '" + category.getName() + "', " +
											"income = " + (category.isIncome() ? 1 : 0) + ", " +
											"parent_id = " + category.getParentId() + " " +
											 "WHERE id = " + category.getId() + ";");
			conn.close();   
		} catch (SQLException e ) {
			log(e);		
			return 1;
		}
		return 0;
	}
	
}
